# -*- coding: utf-8 -*-


import pymysql
from sshtunnel import SSHTunnelForwarder


class ExecuteSQL(object):

    # 传入需要连接的数据库的名称dbname和待执行的sql语句sql
    def __init__(self, dbname, sql):
        self.dbname = dbname
        self.sql = sql

    def execute_sql(self):
        results = ''
        with SSHTunnelForwarder(
                # ('mail.josephrobinettebiden.top', 22),  # 跳板机（堡垒机）B配置
                ('203.57.206.239', 22),  # 跳板机（堡垒机）B配置
                ssh_password='@WSX1qaz',
                ssh_username='root',
                remote_bind_address=('127.0.0.1', 6033),
                # local_bind_address=('127.0.0.1', 33060),
        ) as server:  # 数据库存放服务器C配置

            # 打开数据库连接
            # print server.local_bind_port
            db_connect = pymysql.connect(host='127.0.0.1',  # 本机主机A的IP（必须是这个）
                                         port=server.local_bind_port,
                                         user='root',
                                         passwd='123456',
                                         db=self.dbname)  # 需要连接的数据库的名称

            # 使用cursor()方法获取操作游标
            cursor = db_connect.cursor(pymysql.cursors.DictCursor)

            try:
                # 执行SQL语句
                cursor.execute(self.sql)
                # 获取所有记录列表
                results = cursor.fetchall()
            except Exception as data:
                print(u'Error: 执行查询失败，%s' % data)

            db_connect.close()
            return results


def remote_mysql_query(sql):
    name = "umail"
    # sql = "select * from core_mailbox"
    connect = ExecuteSQL(name, sql)
    # for i in connect.execute_sql():
    #     print i
    return connect.execute_sql()


def get_mail_status(message_id):
    sql = "SELECT `status` FROM `core_send_status` where `message_id`='%s' limit 1" % message_id
    d = remote_mysql_query(sql)
    return d.get('status')


def get_account_list():
    sql = "SELECT `mailbox` FROM `core_mailbox` where `domain_id`=2 and `disabled`='-1'"
    d = remote_mysql_query(sql)
    return [i['mailbox'] for i in d]


if __name__ == '__main__':
    print get_account_list()
